IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SaveMachineDriveVolume' AND type = 'P')
     DROP PROCEDURE dbo.SaveMachineDriveVolume
GO

CREATE PROCEDURE dbo.SaveMachineDriveVolume
     @MachineId                int,
     @PackageExecutionId       bigint,
     @VolumeDeviceId           varchar(128),
     @VolumeMountPoint         varchar(255),
     @VolumeBlockSize          int,
     @VolumeCapacity           bigint,
     @VolumeCompressedFlag     tinyint,
     @VolumeDriveTypeDesc      varchar(30),
     @VolumeFileSystemType     varchar(6),
     @VolumeLabel              varchar(32),
     @VolumeName               varchar(128),
     @VolumeSerialNumber       bigint,
     @ClusterSharedVolumeFlag  tinyint = 0,
     @PackageExecutionIdStr    varchar(30)   = NULL,
     @VolumeDeletedFlag        tinyint       = 0,
     @MachineDriveVolumeId     int           = NULL OUTPUT
AS
/*
 *
 * SaveMachineDriveVolume
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 09/30/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @VolumeIdForSrv int,
             @StartDt        datetime,
             @RC             int,
             @NOW            datetime

     -- Get current date/time
     SELECT @NOW = CURRENT_TIMESTAMP 
     
     -- Handle case of PackageExecutionId
     IF ( @PackageExecutionId IS NULL AND @PackageExecutionIdStr IS NOT NULL )
     BEGIN -- Id value ALWAYS takes precedence
          SELECT @PackageExecutionId = CONVERT( bigint, @PackageExecutionIdStr )
     END
     ELSE 
     BEGIN -- All other cases we set the string to match the id
          SELECT @PackageExecutionIdStr = CONVERT( varchar(30), @PackageExecutionId )
     END

     -- Find out if we have the record already
     IF NOT EXISTS ( SELECT 1
                     FROM   dbo.MachineDriveVolume
                     WHERE  MachineId      = @MachineId 
                     AND    VolumeDeviceId = @VolumeDeviceId )
     BEGIN -- We do not have this record, so we will do an insert, we should
           -- Never have a delete here as well so we will not test for it.
          -- Get the Id 
          SELECT @VolumeIdForSrv = ISNULL(MAX(MachineDriveVolumeId),0) + 1
          FROM   dbo.MachineDriveVolume
          WHERE  MachineId = @MachineId 

          INSERT dbo.MachineDriveVolume
               ( MachineId,
                 MachineDriveVolumeId,
                 StartDt,
                 EndDt,
                 CurrentRecordFlag,
                 PackageExecutionId,
                 VolumeDeletedFlag,
                 VolumeDeviceId,
                 VolumeMountPoint,
                 VolumeBlockSize,
                 VolumeCapacity,
                 VolumeCompressedFlag,
                 VolumeDriveTypeDesc,
                 VolumeFileSystemType,
                 VolumeLabel,
                 VolumeName,
                 VolumeSerialNumber,
                 ClusterSharedVolumeFlag )
          SELECT @MachineId,
                 @VolumeIdForSrv,
                 @NOW, -- StartDt
                 '12/31/9999', -- EndDt
                 1, -- CurrentRecordFlag
                 @PackageExecutionId,
                 0, -- VolumeDeletedFlag
                 @VolumeDeviceId,
                 @VolumeMountPoint,
                 @VolumeBlockSize,
                 @VolumeCapacity,
                 @VolumeCompressedFlag,
                 @VolumeDriveTypeDesc,
                 @VolumeFileSystemType,
                 @VolumeLabel,
                 @VolumeName,
                 @VolumeSerialNumber,
                 @ClusterSharedVolumeFlag
     END -- End New insert
     ELSE
     BEGIN -- We have at least one copy of this record, so we may do an insert
          BEGIN TRANSACTION

          -- Get the Drive Vol Id 
          SELECT @VolumeIdForSrv = MachineDriveVolumeId 
          FROM   dbo.MachineDriveVolume
          WHERE  MachineId      = @MachineId 
          AND    VolumeDeviceId = @VolumeDeviceId

          -- Get the startdt for the current record, if we are here one should
          -- be available, we perform a little trick here as well, taking 
          -- advantage or the ORDER BY  and making sure we have the maximum
          -- StartDt
          SELECT @StartDt = StartDt
          FROM   dbo.MachineDriveVolume
          WHERE  MachineId            = @MachineId 
          AND    MachineDriveVolumeId = @VolumeIdForSrv
          AND    CurrentRecordFlag    = 1
          ORDER BY StartDt

          -- Reset current record flag to zero and set the end date to now minus
          -- 3 ms, 3ms was chosen due to the granularity of the datetype, it does
          -- not handle a difference less than 3ms.  Therefore it ends right before
          -- the new record starts with no overlap.
          UPDATE dbo.MachineDriveVolume 
          SET    EndDt              = DATEADD( ms, -3, @NOW ),
                 CurrentRecordFlag  = 0
          WHERE  MachineId            = @MachineId 
          AND    MachineDriveVolumeId = @VolumeIdForSrv
          AND    CurrentRecordFlag    = 1

          IF ( @VolumeDeletedFlag = 0 )
          BEGIN -- Do regular insert
               INSERT dbo.MachineDriveVolume
                    ( MachineId,
                      MachineDriveVolumeId,
                      StartDt,
                      EndDt,
                      CurrentRecordFlag,
                      PackageExecutionId,
                      VolumeDeletedFlag,
                      VolumeDeviceId,
                      VolumeMountPoint,
                      VolumeBlockSize,
                      VolumeCapacity,
                      VolumeCompressedFlag,
                      VolumeDriveTypeDesc,
                      VolumeFileSystemType,
                      VolumeLabel,
                      VolumeName,
                      VolumeSerialNumber,
                      ClusterSharedVolumeFlag )
               SELECT @MachineId,
                      @VolumeIdForSrv,
                      @NOW, -- StartDt
                      '12/31/9999', -- EndDt
                      1, -- CurrentRecordFlag
                      @PackageExecutionId,
                      0, -- VolumeDeletedFlag
                      @VolumeDeviceId,
                      @VolumeMountPoint,
                      @VolumeBlockSize,
                      @VolumeCapacity,
                      @VolumeCompressedFlag,
                      @VolumeDriveTypeDesc,
                      @VolumeFileSystemType,
                      @VolumeLabel,
                      @VolumeName,
                      @VolumeSerialNumber,
                      @ClusterSharedVolumeFlag
               FROM   dbo.MachineDriveVolume
               WHERE  MachineId            = @MachineId 
               AND    MachineDriveVolumeId = @VolumeIdForSrv
               --AND    PackageExecutionId   = @PEI
               AND    StartDt              = @StartDt
               -- Decide if there will be an insert
               AND  ( VolumeDeletedFlag = 1
                      --1 = 0 -- Placeholder, always false, don't want to insert on accident
                   OR @VolumeMountPoint        != VolumeMountPoint
                   OR @VolumeBlockSize         != VolumeBlockSize
                   OR @VolumeCapacity          != VolumeCapacity
                   OR @VolumeCompressedFlag    != VolumeCompressedFlag
                   OR @VolumeDriveTypeDesc     != VolumeDriveTypeDesc
                   OR @VolumeLabel             != VolumeLabel
                   OR @VolumeName              != VolumeName
                   OR @VolumeSerialNumber      != VolumeSerialNumber
                   OR @ClusterSharedVolumeFlag != ClusterSharedVolumeFlag
                   -- Handle the three NULLable fields
                   OR ( @VolumeMountPoint     IS NULL     AND VolumeMountPoint     IS NOT NULL )
                   OR ( @VolumeMountPoint     IS NOT NULL AND VolumeMountPoint     IS NULL )
                   OR ( @VolumeBlockSize      IS NULL     AND VolumeBlockSize      IS NOT NULL )
                   OR ( @VolumeBlockSize      IS NOT NULL AND VolumeBlockSize      IS NULL )
                   OR ( @VolumeCapacity       IS NULL     AND VolumeCapacity       IS NOT NULL )
                   OR ( @VolumeCapacity       IS NOT NULL AND VolumeCapacity       IS NULL )
                   OR ( @VolumeCompressedFlag IS NULL     AND VolumeCompressedFlag IS NOT NULL )
                   OR ( @VolumeCompressedFlag IS NOT NULL AND VolumeCompressedFlag IS NULL )
                    ) -- End of And-Or section

               SELECT @RC = @@ROWCOUNT 
          END -- End of regular checked insert
          ELSE
          BEGIN -- Do database deleted insert
               -- See if it is already deleted, if so, break!
               IF EXISTS ( SELECT 1 
                           FROM   dbo.MachineDriveVolume 
                           WHERE  MachineId            = @MachineId
                           AND    MachineDriveVolumeId = @VolumeIdForSrv
                           AND    StartDt              = @StartDt
                           AND    VolumeDeletedFlag    = 0 )
               BEGIN -- Not already deleted.
                    INSERT dbo.MachineDriveVolume
                         ( MachineId,
                           MachineDriveVolumeId,
                           StartDt,
                           EndDt,
                           CurrentRecordFlag,
                           PackageExecutionId,
                           VolumeDeletedFlag,
                           VolumeDeviceId,
                           VolumeMountPoint,
                           VolumeBlockSize,
                           VolumeCapacity,
                           VolumeCompressedFlag,
                           VolumeDriveTypeDesc,
                           VolumeFileSystemType,
                           VolumeLabel,
                           VolumeName,
                           VolumeSerialNumber,
                           ClusterSharedVolumeFlag )
                    SELECT MachineId,
                           MachineDriveVolumeId,
                           @NOW, -- StartDt
                           '12/31/9999', -- EndDt
                           1, -- CurrentRecordFlag
                           @PackageExecutionId,
                           1, -- VolumeDeletedFlag
                           VolumeDeviceId,
                           VolumeMountPoint,
                           VolumeBlockSize,
                           VolumeCapacity,
                           VolumeCompressedFlag,
                           VolumeDriveTypeDesc,
                           VolumeFileSystemType,
                           VolumeLabel,
                           VolumeName,
                           VolumeSerialNumber,
                           ClusterSharedVolumeFlag
                    FROM   dbo.MachineDriveVolume
                    WHERE  MachineId            = @MachineId
                    AND    MachineDriveVolumeId = @VolumeIdForSrv
                    --AND    PackageExecutionId   = @PEI
                    AND    StartDt              = @StartDt

                    SELECT @RC = @@ROWCOUNT 
               END
          END

          SET @MachineDriveVolumeId = @VolumeIdForSrv

          IF ( @RC = 1 )
          BEGIN -- We did do an insert so we commit it.
               COMMIT TRANSACTION
          END
          ELSE
          BEGIN -- We did not do any insert, this is not an error, but we will 
                -- do a rollback since we want to keep the last current record.
               ROLLBACK TRANSACTION
          END
     END -- End Existing Insert
END
GO
